package com.mes.service;

import com.mes.entity.ResultItem;
import com.mes.entity.User;
import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import net.sf.json.JSONArray;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class QualityDataService {
	@Autowired
	private Dao dao;

	//加载工序信息
	public List<Map<String,Object>> getProcessList(User user) {
		return dao.query("SELECT EP.Emes_PROCESS_ID,EP.NAME,EP.ROUTE_NO,AU.DEFAULT_PROCESS_ID,AU.NAME AS LOGINNAME FROM Emes_PROCESS EP "
						+ " LEFT JOIN Emes_PROCESS_ROUTE EPR ON EPR.Emes_PROCESS_ROUTE_ID = EP.Emes_PROCESS_ROUTE_ID "
						+ " LEFT JOIN AD_USER AU ON AU.AD_USER_ID = ? "
						+ " WHERE EPR.AD_ORG_ID = ? ORDER BY EP.ROUTE_NO ASC ",user.getId(),user.getOrgId());
	}
	
	//根据工序ID加载采集项目信息
	public List<Map<String, Object>> getDcItems(String processId) {
		List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();
		
		String sql = "select ii.emesq_inspection_item_id id, ii.name, ii.code, ii.data_type, ii.emes_inspectio_enum_data_id enumId " +
				"			, gi.DEFAULT_VALUE defaultVal, gi.FORMULA " +
				"    	from Emesq_Inspection_Item ii, Emesq_Inspection_Group_Item gi, Emesq_QUALITY_RR rr" +
				"      where ii.Emesq_Inspection_Item_id = gi.Emesq_Inspection_Item_id" +
				"        and gi.emesq_inspection_group_id = rr.emesq_inspection_group_id" +
				"		 and rr.emes_process_id = ?" +
				"		order by gi.seq_no";
		List<Map<String, Object>> list = dao.query(sql, processId);
		putAllDcItem(result, list);
		
		sql = "select ii.emesq_inspection_item_id id, ii.name, ii.code, ii.data_type, ii.emes_inspectio_enum_data_id enumId " +
			"	 from Emesq_Inspection_Item ii, Emesq_QUALITY_RR rr" +
			"	where ii.Emesq_Inspection_Item_id = rr.Emesq_Inspection_Item_id" +
			"	  and rr.emes_process_id = ?";
		list = dao.query(sql, processId);
		putAllDcItem(result, list);
		return result;
	}
	
	private void putAllDcItem(List<Map<String, Object>> result, List<Map<String, Object>> list) {
		Map<String, Object> map = null;
		for(Map<String, Object> data : list) {
			map = new HashMap<String, Object>();
			map.put("name", data.get("NAME"));
			map.put("type", data.get("DATA_TYPE"));
			map.put("id", data.get("ID"));
			map.put("formula", data.get("FORMULA"));
			map.put("defaultVal", data.get("DEFAULTVAL"));
			map.put("value", "");

			if ("SELECTOR".equals(data.get("DATA_TYPE"))) {// 选项
				map.put("child", getSelectorData((String)data.get("ENUMID")));// 选项列表
			}
			result.add(map);
		}
	}
	
	private Map<String, Object> getSelectorData(String enumId) {
		String sql = "select di.Emesq_Inspection_Ed_Item_id id, di.result from Emesq_Inspection_Ed_Item di" +
				" where di.Emesq_Inspection_enum_data_id = ?";
		List<Map<String, Object>> list = dao.query(sql, enumId);
		
		Map<String, Object> map = new HashMap<String, Object>();
		for (Map<String, Object> data : list) {
			map.put((String)data.get("ID"), data.get("RESULT"));
		}
		return map;
	}

	//加载花篮对应的订单信息
	public Map<String,Object> getManuOrderInfo(String basketFlowerId) {
		String sql =  "select p.name productName, p.value productCode, to_char(p.weight, '0.000') weight, o.code orderCode, o.qty, o.description," +
				"b.emeso_manu_batch_id manuBatchId,to_char(o.planed_date_from, 'YYYY-MM-DD') dateForm, to_char(o.planed_date_to, 'YYYY-MM-DD') dateTo," +
				"b.emes_process_id processId,ep.name processName,rank() over(order by bj.out_time desc) " +
				"  from emeso_manuorder o, m_product p, emeso_manu_in_material im, emeso_manu_batch b,Emeso_Batch_Join bj,emes_process ep,Eam_Facilities ef " +
				" where rownum = 1" +
				"   and p.m_product_id = im.m_product_id" +
				"   and im.emeso_manuorder_id = o.emeso_manuorder_id" +
				"   and im.emeso_manu_in_material_id = b.emeso_manu_in_material_id" +
				"   and ep.emes_process_id = b.emes_process_id " +
				"   and bj.out_conveyance_id = ef.Eam_Facilities_id " +
				"	and ef.code = ? ";
		return dao.queryUnique(sql, basketFlowerId);
	}

	//保存生产数据采集信息
	@SuppressWarnings("unchecked")
	public String saveQualityData(String code, String manuOrderCode, String manuBatchId, String processId, 
				String description, String codeType, String results, User user, String qualityResultId, String isSubmit) {
		
		Map<String,Object> manuOrderMap = dao.queryUnique("SELECT EM.Emeso_MANUORDER_ID,EM.M_PRODUCT_ID FROM Emeso_MANUORDER EM WHERE EM.CODE = ?", manuOrderCode);
		
		String productId = null;
		if(null != processId && null != manuOrderMap){
			productId = (String)manuOrderMap.get("M_PRODUCT_ID");
		}
		
		//后段检查同一编号同一工序是否已检
		if ("BARCODE".equals(codeType)) {
			String sql = "select code, (select name from emes_process p where p.emes_process_id = qr.emes_process_id) process, is_submit " +
					"from Emesq_QUALITY_RESULT qr where QUALITY_CODE = ?";
			Map<String, Object> map = dao.queryUnique(sql, code);
			if (map != null && "Y".equals(map.get("IS_SUBMIT"))) {
				return "此批次已进行过【" + map.get("PROCESS") + "】抽检，并已提交！";
			}
		}
		
		String uuid = qualityResultId;
		String sql = null;
		int result = 0;
		String insertSql = "INSERT INTO Emesq_QUALITY_RESULT_VALUE(Emesq_QUALITY_RESULT_VALUE_ID,Emesq_QUALITY_RESULT_ID,Emesq_INSPECTION_ITEM_ID," +
			"NAME,TYPE,VALUE,AD_CLIENT_ID,AD_ORG_ID,ISACTIVE,CREATED,CREATEDBY,UPDATED,UPDATEDBY) " +
			"VALUES(SYS_GUID(),?,?,?,?,?,?,?,'Y',SYSDATE,?,SYSDATE,?)";
		
		if (qualityResultId == null || "".equals(qualityResultId) || "null".equals(qualityResultId) || "undefined".equals(qualityResultId)) {
			uuid = StringUtil.getUUID();
			sql = "INSERT INTO Emesq_QUALITY_RESULT(Emesq_QUALITY_RESULT_ID,CODE,M_PRODUCT_ID," +
					("BARCODE".equals(codeType) ? "Emeso_SHEET_BATCH_ID," : "Emeso_MANU_BATCH_ID,") +
					"AD_CLIENT_ID,AD_ORG_ID,ISACTIVE,CREATED,CREATEDBY,UPDATED,UPDATEDBY,DESCRIPTION,QUALITY_CODE,Emes_PROCESS_ID,IS_SUBMIT)"
					+"VALUES(?,GENERATECODE('Emesq_QUALITY_RESULT','CODE',''),?,?,?,?,'Y',SYSDATE,?,SYSDATE,?,?,?,?,'" + isSubmit + "')";
			
			//保存主数据信息
			result += dao.executeSql(sql, uuid, productId, manuBatchId, user.getClientId() ,user.getOrgId(), user.getId(), user.getId(), description, code, processId);
		
			//保存采集项目的信息
			JSONArray jsonArr = JSONArray.fromObject(results);
			List<ResultItem> resultList = (List<ResultItem>) JSONArray.toCollection(jsonArr, ResultItem.class);
			for(ResultItem item : resultList) {
				if (item.getValue() != null && !"".equals(item.getValue())) {
					result += dao.executeSql(insertSql, uuid, item.getId(), item.getName(), item.getType(), item.getValue(), 
							user.getClientId(), user.getOrgId(), user.getId(), user.getId());
				}
			}
		} else {
			sql = "update Emesq_QUALITY_RESULT_VALUE set value = ?, updatedby = ?, updated = sysdate where Emesq_QUALITY_RESULT_VALUE_ID = ?";
			
			JSONArray jsonArr = JSONArray.fromObject(results);
			List<ResultItem> resultList = (List<ResultItem>) JSONArray.toCollection(jsonArr, ResultItem.class);
			for(ResultItem item : resultList) {
				if (item.getResultValueId() != null && !"".equals(item.getResultValueId())) {
					result += dao.executeSql(sql, item.getValue(), user.getId(), item.getResultValueId());
				} else if (item.getValue() != null && !"".equals(item.getValue())) {
					result += dao.executeSql(insertSql, uuid, item.getId(), item.getName(), item.getType(), item.getValue(), 
							user.getClientId(), user.getOrgId(), user.getId(), user.getId());
				}
			}
			
			//更新Emesq_QUALITY_RESULT 中的工序
			String eqSql = "update Emesq_QUALITY_RESULT t set t.emes_process_id=? where t.emesq_quality_result_id=? ";
			dao.executeSql(eqSql, processId, qualityResultId);
		}
		//更新检验的工序
		String processSql = "update Emeso_SHEET_BATCH set Emes_PROCESS_ID= ? " +
				"where Emes_LEVEL_BATCH_ID=(select t.emes_level_batch_id from emes_level_batch t where t.code=? )";
		dao.executeSql(processSql, processId, code);
		
		return result > 0 ? "YES" : "NO";
	}
	
	public Map<String, Object> getQualityResult(String manuBatchId) {
		String sql = "select emesq_quality_result_id result_id, is_submit from emesq_quality_result r where r.emeso_sheet_batch_id = ?";
		Map<String, Object> map = dao.queryUnique(sql, manuBatchId);
		if (map != null) {
			sql = "select emesq_quality_result_value_id value_id, emesq_inspection_item_id item_id, value " +
					"from emesq_quality_result_value v, emesq_quality_result qr" +
					" where qr.emesq_quality_result_id = v.emesq_quality_result_id" +
					"   and qr.emeso_sheet_batch_id = ?";
			
			List<Map<String, Object>> itemValues = dao.query(sql, manuBatchId);
			map.put("itemValues", itemValues);
		}
		return map;
	}
	
	public Map<String, Object> getCodeStatus(String codes) {
		
		String[] code = codes.split(",");
		String sql = "SELECT Emes_LEVEL_BATCH_ID, Emes_LEVEL_BOARD_ID, STATUS, TYPE FROM Emes_LEVEL_BATCH WHERE CODE = ?";
		
		String kbSql = "select emesq_quality_result_id, is_submit from emesq_quality_result r where exists (" +
				"select 1 from emeso_sheet_batch sb where sb.created = (" +
				"  select max(created) from emeso_sheet_batch b where b.emes_level_batch_id = ?" +
				") and sb.emeso_sheet_batch_id = r.emeso_sheet_batch_id" +
				"  and sb.emes_level_batch_id = ?)";
		String xbzSql = "select emesq_quality_result_id, is_submit from emesq_quality_result r " +
				"where exists (select 1 from emeso_sheet_batch sb " +
				"	where sb.emeso_sheet_batch_id = r.emeso_sheet_batch_id and sb.code = ?)";

		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Object> m = null;
		
		for (int i = 0; i < code.length; i++) {
			m = dao.queryUnique(sql, code[i]);
			if (m != null) {
				if ("KB".equals(m.get("TYPE"))) {
					m = dao.queryUnique(kbSql, m.get("Emes_LEVEL_BATCH_ID"), m.get("Emes_LEVEL_BATCH_ID"));
					if (m != null) {
						map.put(code[i], m.get("Emesq_QUALITY_RESULT_ID"));
					}
				} else if ("XBZ".equals(m.get("TYPE"))) {
					m = dao.queryUnique(xbzSql, code[i]);
					if (m != null) {
						map.put(code[i], m.get("Emesq_QUALITY_RESULT_ID"));
					}
				}
			}
		}
		return map;
	}
	
	/**
	 * 更新检验批次
	 */
	public String submitQualityResult(String codes) {
		String[] code = codes.split(",");
		String sql = "SELECT Emes_LEVEL_BATCH_ID, Emes_LEVEL_BOARD_ID, STATUS, TYPE FROM Emes_LEVEL_BATCH WHERE CODE = ?";
		
		String kbSql = "select emesq_quality_result_id, is_submit from emesq_quality_result r where exists (" +
				"select 1 from emeso_sheet_batch sb where sb.created = (" +
				"  select max(created) from emeso_sheet_batch b where b.emes_level_batch_id = ?" +
				") and sb.emeso_sheet_batch_id = r.emeso_sheet_batch_id" +
				"  and sb.emes_level_batch_id = ?)";
		String xbzSql = "select emesq_quality_result_id, is_submit from emesq_quality_result r " +
				"where exists (select 1 from emeso_sheet_batch sb " +
				"	where sb.emeso_sheet_batch_id = r.emeso_sheet_batch_id and sb.code = ?)";
		
		StringBuilder updateSql = new StringBuilder("update emesq_quality_result set is_submit = 'Y' where emesq_quality_result_id in (");

		for (int i = 0; i < code.length; i++) {
			Map<String, Object> map = dao.queryUnique(sql, code[i]);
			if (i > 0) {
				updateSql.append(",");
			}
			if (map != null) {
				if ("KB".equals(map.get("TYPE"))) {
					map = dao.queryUnique(kbSql, map.get("Emes_LEVEL_BATCH_ID"), map.get("Emes_LEVEL_BATCH_ID"));
					if (map == null) {
						return "【" + code[i] + "】未进行数据录入！";
					}
					updateSql.append("'").append(map.get("Emesq_QUALITY_RESULT_ID")).append("'");
				} else if ("XBZ".equals(map.get("TYPE"))) {
					map = dao.queryUnique(xbzSql, code[i]);
					if (map == null) {
						return "【" + code[i] + "】未进行数据录入！";
					}
					updateSql.append("'").append(map.get("Emesq_QUALITY_RESULT_ID")).append("'");
				}
			} else {
				return "【" + code[i] + "】未找到对应的小包装信息！";
			}
		}
		updateSql.append(")");
		
		System.out.println(updateSql);
		
		dao.executeSql(updateSql.toString());
		if(code.length > 0) {
			updateInspectionState(code[0]);
		}
		return null;
	}
	
	public List<Map<String, Object>> getProcessList(String orgId) {
		String sql = "select p.emes_process_id id, p.name from emes_process_route pr, emes_process p " +
				" where pr.emes_process_route_id = p.emes_process_route_id" +
				"   and exists (select 1 from Emesq_Quality_RR rr where rr.emes_process_id = p.emes_process_id)" +
				"   and pr.ad_org_id = ?";
		return dao.query(sql, orgId);
	}
	
	private Map<String, Object> getOrderInfo(String orderId) {
		String sql =  "select p.name productName, p.value productCode, to_char(p.weight, '0.000') weight, o.code orderCode, o.qty, o.description," +
				"to_char(o.planed_date_from, 'YYYY-MM-DD') dateForm, to_char(o.planed_date_to, 'YYYY-MM-DD') dateTo " +
			"  from m_product p, emeso_manuorder o " +
			" where p.m_product_id = o.m_product_id" +
			"	and o.emeso_manuorder_id = ? ";
		
		return dao.queryUnique(sql, orderId);
	}
	
	private String getProcessId(String orgId, int routeNo) {
		String sql = "select emes_process_id from emes_process_route pr, emes_process p " +
				" where p.emes_process_route_id = pr.emes_process_route_id" +
				"   and pr.ad_org_id = ?" +
				"   and p.route_no = ?";
		
		Map<String, Object> map = dao.queryUnique(sql, orgId, routeNo);
		if (map != null) {
			return (String)map.get("Emes_PROCESS_ID");
		}
		return null;
	}
	
	//更新检验状态
	private int updateInspectionState(String code) {
		String sql = "update Emeso_BATCH_INSPECTION set STATES='20'   " +
				"       where Emeso_BATCH_INSPECTION_ID=  " +
				"   (select sb.emeso_batch_inspection_id from emes_level_batch lb, emeso_sheet_batch sb where lb.emes_level_batch_id=sb.emes_level_batch_id " + 
				"      and lb.code=?)";
		int executeSql = dao.executeSql(sql, code);
		
		if(executeSql > 0){
			updateBigBatchState(code);
		}
		return executeSql;
	}
	
	//更新大批次状态
	private int updateBigBatchState(String code){
		String sql = " update emeso_timezone_inspection t "
				+ "  set t.type = '20' "
				+ " where exists "
				+ " (select 1 "
				+ "         from (select count(*) as total "
				+ "                 from emeso_timezone_inspection t, emeso_batch_inspection t1 "
				+ "               where t1.emeso_timezone_inspection_id = "
				+ "                     t.emeso_timezone_inspection_id "
				+ "                 and t1.states = '20' "
				+ "                 and t.emeso_timezone_inspection_id = "
				+ "                     (select emeso_timezone_inspection_id "
				+ "                        from emeso_batch_inspection "
				+ "                       where emeso_batch_inspection_id = (select sb.emeso_batch_inspection_id from emes_level_batch lb, emeso_sheet_batch sb where lb.emes_level_batch_id=sb.emes_level_batch_id " + 
				"      and lb.code=?) )) "
				+ "       where total = "
				+ "             (select count(*) "
				+ "                from emeso_timezone_inspection t, emeso_batch_inspection t1 "
				+ "               where t1.emeso_timezone_inspection_id = "
				+ "                     t.emeso_timezone_inspection_id "
				+ "                 and t.emeso_timezone_inspection_id = "
				+ "                     (select emeso_timezone_inspection_id "
				+ "                        from emeso_batch_inspection "
				+ "                      where emeso_batch_inspection_id = (select sb.emeso_batch_inspection_id from emes_level_batch lb, emeso_sheet_batch sb where lb.emes_level_batch_id=sb.emes_level_batch_id " + 
				"      and lb.code=?) ))) "
				+ " and t.emeso_timezone_inspection_id = "
				+ "   (select emeso_timezone_inspection_id "
				+ "      from emeso_batch_inspection "
				+ "      where emeso_batch_inspection_id = (select sb.emeso_batch_inspection_id from emes_level_batch lb, emeso_sheet_batch sb where lb.emes_level_batch_id=sb.emes_level_batch_id " + 
				"      and lb.code=?))";
		int flag = dao.executeSql(sql, code,code,code);
		
		return flag;
	}
}